package net.bolue.sysmng.db;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.bolue.sysmng.exception.BolueException;
import net.bolue.sysmng.tblbean.TableBean;
import net.bolue.sysmng.util.PageVo;
import net.sf.jsqlparser.JSQLParserException;

/**
 * 数据库操作
 * @author Administrator
 *
 */
public class DbOpUtil {

	private String dbResId;
	private Connection conn = null;
	private boolean isTrans = false;
	
	public DbOpUtil(String dbResId)throws BolueException{
		this.dbResId = dbResId;
		startTrans();
	}
	public DbOpUtil(String dbResId,boolean isTrans)throws BolueException{
		this.dbResId = dbResId;
		this.isTrans = isTrans;
		startTrans();
	}
	
	public DbOpUtil(){
	}
	
	/**
	 * 开启事务
	 * @param dbResId
	 * @param isTrans
	 * @throws BolueException
	 */
	public void startTrans()throws BolueException{
		conn = DbConnector.getConnection(dbResId);
		if(isTrans){
			try {
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				throw new BolueException(9, "数据库标识"+dbResId+"不能开启事务", e);
			}
		}
	}
	
	/**
	 * 关闭事务
	 * @throws BolueException
	 */
	public void commitTrans()throws BolueException{
		if(conn!=null&&isTrans){
			try {
				conn.commit();
			} catch (SQLException e) {
				throw new BolueException(9, "关闭事务失败", e);
			}
		}
		conn = null;
	}
	
	/**
	 * 修改
	 * @param table
	 * @param updateMap
	 * @param whereMap
	 * @return
	 * @throws BolueException
	 */
	public int update(String table,Map<String,Object> updateMap,Map<String,Object> whereMap) throws BolueException{
		Map<String,Object> map = DbCreateSqlUtil.createUpdateSql(table,updateMap,whereMap);
		return excuseDml(map);
	}
	
	public void update(String updateSql,List<Object[]> dataList) throws BolueException{
		try{
			PreparedStatement preparedStatement = conn.prepareStatement(updateSql);
			conn.setAutoCommit(false);
//			System.out.println(updateSql);
			for(Object[] data : dataList){
				for (int i = 0; i < data.length; i++) {
//					System.out.print(data[i]);
					preparedStatement.setObject(i + 1, data[i]);
				}
//				System.out.println();
				preparedStatement.addBatch();
			}
			preparedStatement.executeBatch();
			conn.commit();
		}catch(SQLException e){
			throw new BolueException(9, "SQL:"+updateSql, e);
		}
	}
	
	/**
	 * 插入
	 * @param table
	 * @param insertMap
	 * @return
	 * @throws BolueException
	 */
	public int insert(String table,Map<String,Object> insertMap) throws BolueException{
		Map<String,Object> map = DbCreateSqlUtil.createInsertSql(table,insertMap);
		return excuseDml(map);
	}
	
	public int insert(TableBean tableBean) throws BolueException{
		String tableName = tableBean.tableName();
		Map<String,Object> insertMap = tableBean.entryToMap();
		Map<String,Object> map = DbCreateSqlUtil.createInsertSql(tableName,insertMap);
		return excuseDml(map);
	}
	
	public void insert(String insSql,List<Object[]> dataList) throws BolueException{
		try{
			PreparedStatement preparedStatement = conn.prepareStatement(insSql);
			conn.setAutoCommit(false);
			for(Object[] data : dataList){
				for (int i = 0; i < data.length; i++) {
					preparedStatement.setObject(i + 1, data[i]);
				}
				preparedStatement.addBatch();
			}
			preparedStatement.executeBatch();
			conn.commit();
		}catch(SQLException e){
			throw new BolueException(9, "SQL:"+insSql, e);
		}
	}
	
	/**
	 * 删除
	 * @param dbRes
	 * @param table
	 * @param whereMap
	 * @return
	 * @throws BolueException
	 */
	public int delete(String table,Map<String,Object> whereMap) throws BolueException{
		Map<String,Object> map = DbCreateSqlUtil.createDeleteSql(table,whereMap);
		return excuseDml(map);
	}
	
	/**
	 * 查询列表
	 * @param dbRes
	 * @param table
	 * @param columns
	 * @param whereMap
	 * @return
	 * @throws BolueException
	 */
	public List<Map<String,Object>> query(String table,
			String[] columns,Map<String,Object> whereMap,Map<String,String> orderMap) throws BolueException{
		Map<String,Object> map = DbCreateSqlUtil.createSelectSql(table,columns,whereMap,orderMap);
		return excuseDql(map);
	}
	
	public List<Map<String,Object>> query(String sqlTp,Map<String,Object> whereMap) throws BolueException{
		SelectSqlOp selectSqlOp = null;
		try {
			selectSqlOp = SelectSqlOpUtil.parseSelectSql(sqlTp, whereMap);
		} catch (JSQLParserException e) {
			e.printStackTrace();
		}
		
		if(selectSqlOp!=null){
			String querySql = selectSqlOp.getSelectSql();
			Object[] paramters = selectSqlOp.getParamters();
			return excuseDql(querySql, paramters);
		}
		return null;
	}
	
	public List<Map<String,Object>> query(String sqlTp,Map<String,Object> whereMap,PageVo pageVo) throws BolueException{
		SelectSqlOp selectSqlOp = null;
		try {
			selectSqlOp = SelectSqlOpUtil.parseSelectSql(sqlTp, whereMap);
		} catch (JSQLParserException e) {
			throw new BolueException(9, "SQL:"+sqlTp, e);
		}
		if(selectSqlOp!=null){
			if(pageVo!=null){
				selectSqlOp.setPageVo(pageVo);
			}
			String querySql = selectSqlOp.getSelectSql();
			String countSql = selectSqlOp.getCountSql();
			Object[] paramters = selectSqlOp.getParamters();
			Integer count = excuseCount(countSql,paramters);
			pageVo.setTotalcount(count);
			return excuseDql(querySql, paramters);
		}
		return null;
		
	}
	
	public <T> List<T> query(Class<T> className,String table,
			String[] columns,Map<String,Object> whereMap,Map<String,String> orderMap) throws BolueException{
		Map<String,Object> map = DbCreateSqlUtil.createSelectSql(table,columns,whereMap,orderMap);
		return excuseDql(className,map);
	}
	
	public <T> List<T> query(Class<T> className,String sql,Object[] paramters) throws BolueException{
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("sql", sql);
		map.put("paramters", paramters);
		return excuseDql(className,map);
	}
	
	/**
	 * 查询一个
	 * @param className
	 * @param dbResId
	 * @param sql
	 * @param paramters
	 * @return
	 * @throws BolueException
	 */
	public <T> T getOne(Class<T> className,String sql,
			Object[] paramters) throws BolueException{
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("sql", sql);
		map.put("paramters", paramters);
		List<T> list = excuseDql(className, map);
		if(list.isEmpty()||list.size()<1){
			return null;
		}else{
			return list.get(0);
		}
	}
	
	/**
	 * 查询一个
	 * @param className
	 * @param dbResId
	 * @param table
	 * @param columns
	 * @param whereMap
	 * @return
	 * @throws BolueException
	 */
	public <T> T getOne(Class<T> className,String table,
			String[] columns,Map<String,Object> whereMap) throws BolueException{
		Map<String,Object> map = DbCreateSqlUtil.createSelectSql(table,columns,whereMap,null);
		List<T> list = excuseDql(className,map);
		if(list.isEmpty()||list.size()<1){
			return null;
		}else{
			return list.get(0);
		}
	}
	
	public Map<String,Object> getOne(String table,
			String[] columns,Map<String,Object> whereMap) throws BolueException{
		Map<String,Object> map = DbCreateSqlUtil.createSelectSql(table,columns,whereMap,null);
		List<Map<String,Object>> list = excuseDql(map);
		if(list.isEmpty()||list.size()<1){
			return null;
		}else{
			return list.get(0);
		}
	}
	
	/**
	 * 用于增删改	数据操纵语言DML
	 * @param dataMap	Map{String:sql,Object[]:paramters}
	 * @return
	 * @throws BolueException
	 */
	private int excuseDml(Map<String,Object> dataMap) throws BolueException{
		String sql = (String)dataMap.get("sql");
		Object[] paramters = (Object[])dataMap.get("paramters");
		if(conn==null){
			return 0;
		}
		StringBuffer parameterSb = new StringBuffer();
		try{
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			for (int i = 0; i < paramters.length; i++) {
				preparedStatement.setObject(i + 1, paramters[i]);
				if(i>0){
					parameterSb.append(",");
				}
				parameterSb.append( paramters[i]);
			}
			int delRow = preparedStatement.executeUpdate();
			return delRow;
		}catch(SQLException e){
			throw new BolueException(9, "SQL:"+sql+"\r\n参数:["+parameterSb+"]", e);
		}
	}
	
	/**
	 * 用于查询	数据查询语言DQL
	 * @param dbResId
	 * @param querySql
	 * @param paramters
	 * @return
	 * @throws BolueException
	 */
	private List<Map<String,Object>> excuseDql(String querySql,Object[] paramters) throws BolueException{
		if(querySql==null||"".equals(querySql)){
			return null;
		}
		if(conn==null){
			return null;
		}
		try{
			PreparedStatement preparedStatement = conn.prepareStatement(querySql);
			if(paramters!=null&&paramters.length>0){
				for (int i = 0; i < paramters.length; i++) {
					preparedStatement.setObject(i + 1, paramters[i]);
				}
			}
			ResultSet rs = preparedStatement.executeQuery();
			return resultToListMap(rs);
		}catch(SQLException e){
			throw new BolueException(9,"SQL:"+querySql,e);
		}
	}
	
	/**
	 * 用于查询	数据查询语言DQL
	 * @param dbRes
	 * @param dataMap	Map{String:sql,Object[]:paramters}
	 * @return
	 * @throws BolueException
	 */
	private List<Map<String,Object>> excuseDql(Map<String,Object> dataMap) throws BolueException{
		String sql = (String)dataMap.get("sql");
		Object[] paramters = (Object[])dataMap.get("paramters");
		if(conn==null){
			return null;
		}
		try{
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			if(paramters!=null&&paramters.length>0){
				for (int i = 0; i < paramters.length; i++) {
					preparedStatement.setObject(i + 1, paramters[i]);
				}
			}
			ResultSet rs = preparedStatement.executeQuery();
			return resultToListMap(rs);
		}catch(SQLException e){
			throw new BolueException(9,"SQL:"+sql,e);
		}
	}
	
	/**
	 * 用于查询	数据查询语言DQL
	 * @param className
	 * @param dbResId
	 * @param dataMap
	 * @return
	 * @throws BolueException
	 */
	private <T> List<T> excuseDql(Class<T> className,Map<String,Object> dataMap) throws BolueException{
		String sql = (String)dataMap.get("sql");
		Object[] paramters = (Object[])dataMap.get("paramters");
		if(conn==null){
			return null;
		}
		try{
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			if(paramters!=null&&paramters.length>0){
				for (int i = 0; i < paramters.length; i++) {
					preparedStatement.setObject(i + 1, paramters[i]);
				}
			}
			ResultSet rs = preparedStatement.executeQuery();
			return resultToListEntry(className,rs);
		}catch(SQLException e){
			throw new BolueException(9, "SQL:"+sql, e);
		}
	}
	
	private Integer excuseCount(String countSql,Object[] paramters) throws BolueException{
		if(countSql==null||"".equals(countSql)){
			return null;
		}
		try{
			if(conn==null){
				return 0;
			}
			PreparedStatement preparedStatement = conn.prepareStatement(countSql);
			if(paramters!=null&&paramters.length>0){
				for (int i = 0; i < paramters.length; i++) {
					preparedStatement.setObject(i + 1, paramters[i]);
				}
			}
			ResultSet rs = preparedStatement.executeQuery();
			Integer count = 0;
			if (rs.next()){
				count = rs.getInt(1);
			}
			return count;
		}catch(SQLException e){
			throw new BolueException(9,"SQL:"+countSql,e);
		}
	}
	
	/**
	 * 查询结果集转换
	 * @param rs
	 * @return
	 * @throws BolueException
	 */
	private static List<Map<String,Object>> resultToListMap(ResultSet rs) throws BolueException {
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try{
			while (rs.next()) {
				ResultSetMetaData md = rs.getMetaData();
				Map<String,Object> map = new HashMap<String,Object>();
				for (int i = 1; i <= md.getColumnCount(); i++) {
					map.put(md.getColumnLabel(i), rs.getObject(i));
				}
				list.add(map);
			}
		}catch(SQLException e){
			throw new BolueException(9,e.getLocalizedMessage(),e);
		}
		return list;
	}
	
	/**
	 * 查询结果集转换
	 * @param entryClass
	 * @param rs
	 * @return
	 * @throws BolueException
	 */
	private static <T>List<T> resultToListEntry(Class<T> entryClass,ResultSet rs) throws BolueException {
		List<T> list = new ArrayList<T>();
		try{
			while (rs.next()) {
				ResultSetMetaData md = rs.getMetaData();
				Map<String,Object> map = new HashMap<String,Object>();
				for (int i = 1; i <= md.getColumnCount(); i++) {
					map.put(md.getColumnLabel(i), rs.getObject(i));
				}
				Method method = entryClass.getMethod("mapToEntry", Map.class);
				T t = entryClass.newInstance();
				Object o = method.invoke(t, map);
				t = (T)o;
				list.add(t);
			}
		}catch(Exception e){
			throw new BolueException(9,"查询结果集转换",e);
		}
		return list;
	}
	
}
